﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using AdminHome.Models;

namespace MVCSiteJDG.Dao.DataAccess
{
    public class Reader
    {
        public static String getShoppingCartID(String accountID)
        {
             System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");

            System.Data.OleDb.OleDbCommand getShoppingCartCommand = new System.Data.OleDb.OleDbCommand("select shoppingCartID from ShoppingCart where AccountID ='" + accountID + "'", connection);

            connection.Open();
            System.Data.OleDb.OleDbDataReader reader = getShoppingCartCommand.ExecuteReader();
            var SqlData = "";
            SqlData = reader.GetValue(0).ToString();

            return SqlData;
        }
        public static ShoppingCart getShoppingCartItemsProdIds(String shoppingCartID)
        {

             System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");

            System.Data.OleDb.OleDbCommand getShoppingCartCommand = new System.Data.OleDb.OleDbCommand("select productID from CartItem where cartID ='" + shoppingCartID + "'", connection);

            connection.Open();
            System.Data.OleDb.OleDbDataReader reader = getShoppingCartCommand.ExecuteReader();
            var shoppingCart = new ShoppingCart();

            while (reader.Read())
            {
                string Temp = reader.GetValue(0).ToString();
                shoppingCart.addProduct(Temp);
            }

            return shoppingCart;
        }

        public static List<List<String>> getProductList(int minRange, int maxRange)
        {
             System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");

            System.Data.OleDb.OleDbCommand getAccountDetailsCommand = new System.Data.OleDb.OleDbCommand("select * from product where productID >" + minRange + " and productID < " + maxRange + "", connection);

            connection.Open();
            System.Data.OleDb.OleDbDataReader reader = getAccountDetailsCommand.ExecuteReader();
            var data1 = new List<String>();
            var data = new List<List<String>>();

            while (reader.Read())
            {
                data1.Add(reader.GetString(0));
                data1.Add(reader.GetString(1));
                data1.Add(reader.GetString(2));
                data1.Add(reader.GetString(3));
                data1.Add(reader.GetString(4));
                var num1 = (String)reader.GetValue(5).ToString();
                var num2 = (String)reader.GetValue(6).ToString();
                data1.Add(num1);
                data1.Add(num2);
                data.Add(data1);
            }
            return data;
        }
        public static List<String> readAccountDetails(String UserName)
        {
             System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
            System.Data.OleDb.OleDbCommand getAccountDetailsCommand = new System.Data.OleDb.OleDbCommand("select accountName, accountRegDate, accountLastLogin, accountEmail from Account where accountName = '" + UserName + "'", connection);
            connection.Open();
            System.Data.OleDb.OleDbDataReader reader = getAccountDetailsCommand.ExecuteReader();
            String[] SqlData = new String[5];
            while (reader.Read())
            {

                SqlData[0] = reader.GetString(0);
                SqlData[1] = reader.GetString(1);
                SqlData[2] = reader.GetString(2);
                SqlData[3] = reader.GetString(3);
            }
            var data = new List<String>();
            data.Add(SqlData[0]);
            data.Add(SqlData[1]);
            data.Add(SqlData[2]);
            data.Add(SqlData[3]);
            reader.Dispose();
            return data;
        }
        //public static List<string> readApplication(string Id)
        //{ try
        //    {
        //         System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
        //        System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("SELECT * FROM APPLICATION");

        //        connection.Open();
        //        var reader = command.ExecuteReader();
        //        var list = new List<string>();
        //        while (reader.Read())
        //        {
        //            list.Add(reader.GetString(0));
        //            list.Add(reader.GetString(1));
        //            list.Add(reader.GetString(0));
        //        }
        //    return list;
        //    }
        //    catch (Exception)
        //    {
        //        return new List<string>();
        //    }
        //}
        public static List<string> readAllAssets()
        {
            try
            {
                 System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("SELECT * FROM ASSET");

                connection.Open();
                var reader = command.ExecuteReader();
                var list = new List<string>();
                var returnThis = new List<List<String>>();
                while (reader.Read())
                {

                    list.Add(reader.GetString(0));
                    list.Add(reader.GetString(1));
                    list.Add(reader.GetString(2));
                    returnThis.Add(list);
                }
                return list;
            }
            catch (Exception)
            {
                return new List<string>();
            }

        }
        public static List<string> readAsset(string assetID)
        {
            try
            {
                System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("SELECT * FROM ASSET where assetID = " + assetID);

                connection.Open();
                var reader = command.ExecuteReader();
                var list = new List<string>();
                var returnThis = new List<List<String>>();
                while (reader.Read())
                {

                    list.Add(reader.GetString(0));
                    list.Add(reader.GetString(1));
                    list.Add(reader.GetString(2));
                    returnThis.Add(list);
                }
                return list;
            }
            catch (Exception)
            {
                return new List<string>();
            }

        }
        public static List<string> readCartItem(string cartItemID)
        {
            try
            {
                 System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("SELECT * FROM CARTITEM where cartItemID=" + cartItemID);

                connection.Open();
                var reader = command.ExecuteReader();
                var list = new List<string>();
                while (reader.Read())
                {

                    list.Add(reader.GetString(0));
                    list.Add(reader.GetString(1));
                    list.Add(reader.GetString(0));
                }
                return list;
            }
            catch (Exception)
            {
                return new List<string>();
            }
        }
        public static List<string> readCatalog(string catID)
        {
            try
            {
                 System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("SELECT * FROM CATALOG where catID = " + catID);

                connection.Open();
                var reader = command.ExecuteReader();
                var list = new List<string>();
                while (reader.Read())
                {

                    list.Add(reader.GetString(0));
                    list.Add(reader.GetString(1));
                    list.Add(reader.GetString(0));
                }
                return list;
            }
            catch (Exception)
            {
                return new List<string>();
            }
        }
        //public static List<string> readCatalogProduct(string Id)
        //{
        //    try
        //    {
        //         System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
        //        System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("SELECT * FROM CATALOGPRODUCT");

        //        connection.Open();
        //        var reader = command.ExecuteReader();
        //        var list = new List<string>();
        //        while (reader.Read())
        //        {

        //            list.Add(reader.GetString(0));
        //            list.Add(reader.GetString(1));
        //            list.Add(reader.GetString(0));
        //        }
        //        return list;
        //    }
        //    catch (Exception)
        //    {
        //        return new List<string>();
        //    }
        //}
        public static List<string> readCostCentre(string costCentreID)
        {
            try
            {
                 System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("SELECT * FROM COSTCENTRE where costCentreID =" + costCentreID);

                connection.Open();
                var reader = command.ExecuteReader();
                var list = new List<string>();
                while (reader.Read())
                {

                    list.Add(reader.GetString(0));
                    list.Add(reader.GetString(1));
                    list.Add(reader.GetString(0));
                }
                return list;
            }
            catch (Exception)
            {
                return new List<string>();
            }

        }
        public static List<string> readDepartment(string departmentID)
        {
            try
            {
                 System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("SELECT * FROM DEPARTMENT where departmentID = " + departmentID);

                connection.Open();
                var reader = command.ExecuteReader();
                var list = new List<string>();
                while (reader.Read())
                {

                    list.Add(reader.GetString(0));
                    list.Add(reader.GetString(1));
                    list.Add(reader.GetString(0));
                }
                return list;
            }
            catch (Exception)
            {
                return new List<string>();
            }

        }
        public static List<List<string>> readAllDepartment()
        {
            try
            {
                System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("SELECT * FROM DEPARTMENT");

                connection.Open();
                var reader = command.ExecuteReader();
                var record = new List<string>();
                var list = new List<List<string>>();
                while (reader.Read())
                {

                    record.Add(reader.GetValue(0).ToString());
                    record.Add(reader.GetValue(1).ToString());
                    record.Add(reader.GetValue(2).ToString());
                    record.Add(reader.GetValue(4).ToString());
                    list.Add(record);
                }
                return list;
            }
            catch (Exception)
            {
                var returnData = new List<List<string>>();
                return returnData;
            }

        }
        //public static List<string> readEmployee(string Id)
        //{
        //    try
        //    {
        //         System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
        //        System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("SELECT * FROM EMPLOYEE");

        //        connection.Open();
        //        var reader = command.ExecuteReader();
        //        var list = new List<string>();
        //        while (reader.Read())
        //        {

        //            list.Add(reader.GetString(0));
        //            list.Add(reader.GetString(1));
        //            list.Add(reader.GetString(0));
        //        }
        //        return list;
        //    }
        //    catch (Exception)
        //    {
        //        return new List<string>();
        //    }

        //}
        public static List<string> readLog(string logID)
        {
            try
            {
                 System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("SELECT * FROM LOG where logID = " + logID);

                connection.Open();
                var reader = command.ExecuteReader();
                var list = new List<string>();
                while (reader.Read())
                {

                    list.Add(reader.GetString(0));
                    list.Add(reader.GetString(1));
                    list.Add(reader.GetString(0));
                }
                return list;
            }
            catch (Exception)
            {
                return new List<string>();
            }

        }
        public static List<string> readMainCategory(string mainCategoryID)
        {
            try
            {
                 System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("SELECT * FROM MAINCATEGORY where mainCategoryID = " + mainCategoryID);

                connection.Open();
                var reader = command.ExecuteReader();
                var list = new List<string>();
                while (reader.Read())
                {

                    list.Add(reader.GetString(0));
                    list.Add(reader.GetString(1));
                    list.Add(reader.GetString(0));
                }
                return list;
            }
            catch (Exception)
            {
                return new List<string>();
            }

        }
        public static List<string> readOrders(string orderID)
        {
            try
            {
                 System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("SELECT * FROM ORDERs where orderID = " + orderID);

                connection.Open();
                var reader = command.ExecuteReader();
                var list = new List<string>();
                while (reader.Read())
                {

                    list.Add(reader.GetString(0));
                    list.Add(reader.GetString(1));
                    list.Add(reader.GetString(0));
                }
                return list;
            }
            catch (Exception)
            {
                return new List<string>();
            }

        }
        public static List<string> readOrderLineItem(string orderLineID)
        {
            try
            {
                 System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("SELECT * FROM ORDERLINEITEM where orderLineID = " + orderLineID);

                connection.Open();
                var reader = command.ExecuteReader();
                var list = new List<string>();
                while (reader.Read())
                {

                    list.Add(reader.GetString(0));
                    list.Add(reader.GetString(1));
                    list.Add(reader.GetString(0));
                }
                return list;
            }
            catch (Exception)
            {
                return new List<string>();
            }
        }
        //public static List<string> readOrderStatus(string Id)
        //{
        //    try
        //    {
        //         System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
        //        System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("SELECT * FROM ORDERSTATUS");

        //        connection.Open();
        //        var reader = command.ExecuteReader();
        //        var list = new List<string>();
        //        while (reader.Read())
        //        {

        //            list.Add(reader.GetString(0));
        //            list.Add(reader.GetString(1));
        //            list.Add(reader.GetString(0));
        //        }
        //        return list;
        //    }
        //    catch (Exception)
        //    {
        //        return new List<string>();
        //    }
        //}
        public static List<string> readProduct(string productID)
        {
            try
            {
                 System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("SELECT * FROM Product where productID = " + productID,connection);

                connection.Open();
                var reader = command.ExecuteReader();
                var list = new List<string>();
                while (reader.Read())
                {

                    list.Add(reader.GetString(0));
                    list.Add(reader.GetString(1));
                    list.Add(reader.GetString(2));
                }
                return list;
            }
            catch (Exception)
            {
                return new List<string>();
            }
        }
        public static List<string> readRequest(string requestID)
        {
            try
            {
                 System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("SELECT * FROM Request where requestID = " + requestID);

                connection.Open();
                var reader = command.ExecuteReader();
                var list = new List<string>();
                while (reader.Read())
                {

                    list.Add(reader.GetString(0));
                    list.Add(reader.GetString(1));
                    list.Add(reader.GetString(2));
                }
                return list;
            }
            catch (Exception)
            {
                return new List<string>();
            }
        }
        public static List<string> readReview(string reviewID)
        {
            try
            {
                 System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("SELECT * FROM REVIEW where reviewID = " + reviewID);

                connection.Open();
                var reader = command.ExecuteReader();
                var list = new List<string>();
                while (reader.Read())
                {

                    list.Add(reader.GetString(0));
                    list.Add(reader.GetString(1));
                    list.Add(reader.GetString(2));
                }
                return list;
            }
            catch (Exception)
            {
                return new List<string>();
            }
        }
        public static List<string> readShoppingCart(string cartID)
        {
            try
            {
                 System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("SELECT * FROM ShoppingCart where cartID = " + cartID);

                connection.Open();
                var reader = command.ExecuteReader();
                var list = new List<string>();
                while (reader.Read())
                {

                    list.Add(reader.GetString(0));
                    list.Add(reader.GetString(1));
                    list.Add(reader.GetString(2));
                }
                return list;
            }
            catch (Exception)
            {
                return new List<string>();
            }
        }
        public static List<string> readShoppingHistory(string shoppingHistoryID)
        {
            try
            {
                 System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("SELECT * FROM ShoppingHistory where shoppingHistoryID = " + shoppingHistoryID);

                connection.Open();
                var reader = command.ExecuteReader();
                var list = new List<string>();
                while (reader.Read())
                {

                    list.Add(reader.GetString(0));
                    list.Add(reader.GetString(1));
                    list.Add(reader.GetString(2));
                }
                return list;
            }
            catch (Exception)
            {
                return new List<string>();
            }
        }
        public static List<string> readSubCategory(string subCategoryID)
        {
            try
            {
                 System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("SELECT * FROM SUBCATEGORY where subCategoryID = " + subCategoryID);

                connection.Open();
                var reader = command.ExecuteReader();
                var list = new List<string>();
                while (reader.Read())
                {

                    list.Add(reader.GetString(0));
                    list.Add(reader.GetString(1));
                    list.Add(reader.GetString(2));
                }
                return list;
            }
            catch (Exception)
            {
                return new List<string>();
            }

        }
        public static List<string> readSupplier(string supplierID)
        {
            try
            {
                 System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("SELECT * FROM SUPPLIER where supplierID = " + supplierID);

                connection.Open();
                var reader = command.ExecuteReader();
                var list = new List<string>();
                while (reader.Read())
                {

                    list.Add(reader.GetString(0));
                    list.Add(reader.GetString(1));
                    list.Add(reader.GetString(2));
                }
                return list;
            }
            catch (Exception)
            {
                return new List<string>();
            }

        }
        public static List<string> readTechnician(string technicianID)
        {
            try
            {
                 System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("SELECT * FROM TECHNICIAN where technicianID = " + technicianID);

                connection.Open();
                var reader = command.ExecuteReader();
                var list = new List<string>();
                while (reader.Read())
                {
                    list.Add(reader.GetString(0));
                    list.Add(reader.GetString(1));
                    list.Add(reader.GetString(2));
                }
                return list;
            }
            catch (Exception)
            {
                return new List<string>();
            }

        }
    }
}